﻿using CDMS.Common;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using CDMS.Entities;

namespace CDMS.DAL
{
    public class Contract_DAL
    {
        DBAccess db;
        string tableName;
        public Contract_DAL()
        {
            db = new DBAccess();
            this.tableName = "Contract";
        }

        public DataTable GetALL()
        {
            DataTable retVal = null;
            string sql = string.Format("{0}_GetAll", tableName);
            retVal = db.GetDataTable(sql);
            return retVal;
        }

        public DataTable GetALLActivated()
        {
            DataTable retVal = null;
            string sql = string.Format("{0}_GetALLActivated", tableName);
            retVal = db.GetDataTable(sql);
            return retVal;
        }

        public DataTable Search(int CustomerID, DateTime fromSignDate, DateTime toSignDate, int ContractTypeID, int ContractStatusID)
        {
            DataTable retVal = null;

            SqlParameter pF;
            if (fromSignDate == DateTime.MinValue)
                pF = new SqlParameter("FromSignDate", DBNull.Value);
            else
                pF = new SqlParameter("FromSignDate", fromSignDate);

            SqlParameter pT;
            if (toSignDate == DateTime.MinValue)
                pT = new SqlParameter("toSignDate", DBNull.Value);
            else
                pT = new SqlParameter("toSignDate", toSignDate);

            SqlParameter[] paramArray = new SqlParameter[] 
            { 
                new SqlParameter("CustomerID",CustomerID),
                new SqlParameter("ContractTypeID",ContractTypeID),
                new SqlParameter("ContractStatusID",ContractStatusID),
                pF,pT
            };

            string sql = string.Format("{0}_Search", tableName);
            retVal = db.GetDataTable(sql, paramArray);
            return retVal;
        }

        public Contract GetOne(int Contract_ID)
        {

            Contract retVal = null;
            DataTable dt = new DataTable();

            SqlParameter[] paramArray = new SqlParameter[] 
            { 
                new SqlParameter(tableName+"_ID",Contract_ID)
            };
            string sql = string.Format("{0}_GetOne", tableName);

            dt = db.GetDataTable(sql, paramArray);
            if (dt != null)
                foreach (DataRow dr in dt.Rows)
                {
                    retVal = new Contract();
                    retVal.FillDataProperty(dr);
                }
            return retVal;
        }

        public int Insert(Contract entity)
        {
            int retVal;
            SqlParameter[] paramArray = new SqlParameter[] 
            { 
                new SqlParameter("Contract_Content",entity.Contract_Content),
                new SqlParameter("Contract_ContractStatusID",entity.Contract_ContractStatusID),
                new SqlParameter("Contract_ContractTypeID",entity.Contract_ContractTypeID),
                new SqlParameter("Contract_CustomerID",entity.Contract_CustomerID),
                new SqlParameter("Contract_Description",entity.Contract_Description),
                new SqlParameter("Contract_EffectDate",entity.Contract_EffectDate),
                new SqlParameter("Contract_ExpireDate",entity.Contract_ExpireDate),
                new SqlParameter("Contract_No",entity.Contract_No),
                new SqlParameter("Contract_Remind",entity.Contract_Remind),
                new SqlParameter("Contract_SignDate",entity.Contract_SignDate),
                new SqlParameter("Contract_Value",entity.Contract_Value)
            };
            string sql = string.Format("{0}_Insert", tableName);
            retVal = Convert.ToInt32(db.ExecuteScalar(sql, paramArray));

            return retVal;
        }
        public bool Update(Contract entity)
        {
            SqlParameter[] paramArray = new SqlParameter[] 
            { 
                new SqlParameter("Contract_Content",entity.Contract_Content),
                new SqlParameter("Contract_ContractStatusID",entity.Contract_ContractStatusID),
                new SqlParameter("Contract_ContractTypeID",entity.Contract_ContractTypeID),
                new SqlParameter("Contract_CustomerID",entity.Contract_CustomerID),
                new SqlParameter("Contract_Description",entity.Contract_Description),
                new SqlParameter("Contract_EffectDate",entity.Contract_EffectDate),
                new SqlParameter("Contract_ExpireDate",entity.Contract_ExpireDate),
                new SqlParameter("Contract_No",entity.Contract_No),
                new SqlParameter("Contract_Remind",entity.Contract_Remind),
                new SqlParameter("Contract_SignDate",entity.Contract_SignDate),
                new SqlParameter("Contract_Value",entity.Contract_Value),
                new SqlParameter("Contract_ID",entity.Contract_ID)
            };

            string sql = string.Format("{0}_Update", tableName);

            return Convert.ToInt32(db.ExecuteNonQuery(sql, paramArray)) > 0;
        }

        public bool Exists(Contract entity)
        {
            SqlParameter[] paramArray = new SqlParameter[] 
            { 
                new SqlParameter("Contract_No",entity.Contract_No),
                new SqlParameter("Contract_ID",entity.Contract_ID)
            };

            string sql = string.Format("{0}_Exists", tableName);


            return Convert.ToInt32(db.ExecuteScalar(sql, paramArray)) > 0;
        }

        public bool Delete(int Contract_ID)
        {
            SqlParameter[] paramArray = new SqlParameter[] 
            { 
                new SqlParameter("Contract_ID",Contract_ID)
            };

            string sql = string.Format("{0}_Delete", tableName);
            return Convert.ToInt32(db.ExecuteNonQuery(sql, paramArray)) > 0;
        }
    }
}
